﻿
using Dapper;
using System;
using System.Collections.Generic;
using System.Data;
using System.Text;
using System.Threading.Tasks;
using BCCommon;
using BCEntity.TMS.SpecificServiceTypeOverWrite;
using BCEntity.TMS.SpecificServiceType;

namespace BCData.TMS.SpecificServiceTypeOverWrite
{
    public class SpecificServiceTypeOverWriteData : ISpecificServiceTypeOverWriteData
    {
        private readonly IDatabaseContext database;
        private readonly string QUERY = @"SELECT
		    SpecificServiceTypeOverwriteId,
		    SpecificServiceTypeId,
		    Enabled,
		    ProvinceCode,
		    CityCode,
		    CountyCode,
		    AgentRecordId,
		    CreateAdminId,
		    CreateAdminName,
		    CreateTime,
		    UpdateTime,
		    EditAdminId,
		    EditAdminName
        FROM
            specificservicetypeoverwrite";

        #region 构造函数
        public SpecificServiceTypeOverWriteData(IDatabaseContext database)
        {
            this.database = database;
        }
        #endregion

        #region InsertAsync
        public async Task<SpecificServiceTypeOverWriteEntity> InsertAsync(SpecificServiceTypeOverWriteEntity entity)
        {
            return await this.database.Master.InsertAsync(entity);
        }
        #endregion

        #region updateAsync
        public async Task<SpecificServiceTypeOverWriteEntity> UpdateAsync(SpecificServiceTypeOverWriteEntity entity)
        {
            return await this.database.Master.UpdateAsync(entity);
        }
        #endregion 

        #region Delete
        public async Task<bool> DeleteAsync(int specificServiceTypeOverWriteId)
        {
            string sql = "DELETE FROM specificservicetypeoverwrite WHERE SpecificServiceTypeOverWriteId = ?specificServiceTypeOverWriteId";
            DynamicParameters parameters = new DynamicParameters();
            parameters.Add("?SpecificServiceTypeOverWriteId", specificServiceTypeOverWriteId, DbType.Int64);
            return await this.database.Master.ExecuteAsync(sql, parameters, database.Transaction) > 0;
        }
        #endregion

        #region Delete
        public async Task<bool> DeleteByFatherIdAsync(int specificServiceTypeId)
        {
            string sql = "DELETE FROM specificservicetypeoverwrite WHERE SpecificServiceTypeId = ?specificServiceTypeId";
            DynamicParameters parameters = new DynamicParameters();
            parameters.Add("?SpecificServiceTypeId", specificServiceTypeId, DbType.Int64);
            return await this.database.Master.ExecuteAsync(sql, parameters, database.Transaction) > 0;
        }
        #endregion

        #region GetById
        public async Task<SpecificServiceTypeOverWriteEntity> GetEntityAsync(int specificServiceTypeOverWriteId)
        {
            string sql = QUERY + " WHERE  SpecificServiceTypeOverWriteId = ?specificServiceTypeOverWriteId";

            DynamicParameters parameters = new DynamicParameters();
            parameters.Add("?SpecificServiceTypeOverWriteId", specificServiceTypeOverWriteId, DbType.Int64);
            return await this.database.Master.QueryFirstOrDefaultAsync<SpecificServiceTypeOverWriteEntity>(sql, parameters);
        }
        #endregion
         

        #region GetListAsync 查询特殊服务类型重写记录
        public async Task<(IEnumerable<SpecificServiceTypeViewEntity>, int)> GetListAsync(string provinceCode, string cityCode, string countyCode,long agentRecordId, int pageIndex, int pageSize)
        {

            DynamicParameters parameters = new DynamicParameters();
            //查询条件
            List<string> wheres = new List<string>();
            string sqlView = string.Empty;
            string sqlViewCount = string.Empty;

            if (string.IsNullOrWhiteSpace(countyCode) || string.IsNullOrWhiteSpace(provinceCode) || string.IsNullOrWhiteSpace(cityCode))
            {
                sqlView = GetSqlStr(false);
                sqlViewCount = GetSqlCountStr(false);
            }
            else
            {
                if (!string.IsNullOrWhiteSpace(provinceCode))
                {
                    wheres.Add("B.ProvinceCode = ?ProvinceCode ");
                    parameters.Add("?ProvinceCode", provinceCode.Trim(), DbType.AnsiString);
                }
                if (!string.IsNullOrWhiteSpace(cityCode))
                {
                    wheres.Add(" B.CityCode =?CityCode");
                    parameters.Add("?CityCode", cityCode.Trim(), DbType.AnsiString);
                }
                if (!string.IsNullOrWhiteSpace(countyCode))
                {
                    wheres.Add(" B.CountyCode =?CountyCode ");
                    parameters.Add("?CountyCode", countyCode.Trim(), DbType.AnsiString);
                }

                sqlView = GetSqlStr(true);
                sqlViewCount = GetSqlCountStr(true);
            }

            wheres.Add("AgentRecordId=?AgentRecordId");
            parameters.Add("?AgentRecordId", agentRecordId, DbType.Int64);


            string sql = string.Format(@"{0} {2}  LIMIT {3},{4} ; {1} {2}",
                   sqlView,
                   sqlViewCount,
                  wheres.Count > 0 ? " AND " + string.Join(" AND ", wheres) : "",
                  (pageIndex == 0 ? 0 : pageIndex - 1) * pageSize,
                  pageSize
                  );

            return await this.database.QueryMultipleAsync<SpecificServiceTypeViewEntity, int>(sql, parameters);
        }
        //获取SQL
        private string GetSqlStr(bool isHasArea)
        {
            if (isHasArea)
            {
                return @" SELECT 		    
                                B.SpecificServiceTypeOverwriteId,  
                                A.SpecificServiceTypeId,
                                B.Enabled , 
                                A.ServiceTypeName,
                                A.HasRemark,
                                A.ServiceRemark,  
                                B.AgentRecordId,
                                C.ProvinceCode, 
                                C.ProvinceName, 
                                C.CityCode, 
                                C.CityName, 
                                C.CountyCode, 
                                C.CountyName
                                FROM specificservicetype A
                                INNER JOIN
                                (  SELECT * FROM specificservicetypeoverwrite  ) B
                                ON A.SpecificServiceTypeId = B.SpecificServiceTypeId 
                                INNER JOIN county C
                                ON B.ProvinceCode = C.ProvinceCode AND B.CityCode = C.CityCode AND B.CountyCode = C.CountyCode  ";
            }
            else
            {
                return @" SELECT 		    
                                B.SpecificServiceTypeOverwriteId,  
                                A.SpecificServiceTypeId,
                                B.Enabled , 
                                A.ServiceTypeName,
                                A.HasRemark,
                                A.ServiceRemark,  
                                B.AgentRecordId,
                                C.ProvinceCode, 
                                C.ProvinceName, 
                                C.CityCode, 
                                C.CityName, 
                                C.CountyCode, 
                                C.CountyName
                                FROM specificservicetype A
                                INNER JOIN
                                (  SELECT * FROM specificservicetypeoverwrite  ) B
                                ON A.SpecificServiceTypeId = B.SpecificServiceTypeId 
                                INNER JOIN county C
                                ON B.ProvinceCode = C.ProvinceCode AND B.CityCode = C.CityCode AND B.CountyCode = C.CountyCode   ";
            }
        }
        //获取SQLCount
        private string GetSqlCountStr(bool isHasArea)
        {
            if (isHasArea)
            {
                return @" SELECT  COUNT(1)
                                FROM specificservicetype A
                                INNER JOIN
                                (  SELECT * FROM specificservicetypeoverwrite  ) B
                                ON A.SpecificServiceTypeId = B.SpecificServiceTypeId 
                                INNER JOIN county C
                                ON B.ProvinceCode = C.ProvinceCode AND B.CityCode = C.CityCode AND B.CountyCode = C.CountyCode  ";
            }
            else
            {
                return @" SELECT  COUNT(1) 
                                FROM specificservicetype A
                                INNER JOIN
                                (  SELECT * FROM specificservicetypeoverwrite  ) B
                                ON A.SpecificServiceTypeId = B.SpecificServiceTypeId 
                                INNER JOIN county C
                                ON B.ProvinceCode = C.ProvinceCode AND B.CityCode = C.CityCode AND B.CountyCode = C.CountyCode   ";
            }
        }
        #endregion



        #region GetListAsync 查询运输特殊服务费配置重写记录条数
        public async Task<int> GetCountAsync(string provinceCode, string cityCode, string countyCode, long agentRecordId, int specificServiceTypeId)
        {
            DynamicParameters parameters = new DynamicParameters();
            //查询条件
            List<string> wheres = new List<string>();


            if (!string.IsNullOrWhiteSpace(provinceCode))
            {
                wheres.Add("ProvinceCode =?ProvinceCode");
                parameters.Add("?ProvinceCode", provinceCode.Trim(), DbType.AnsiString);
            }
            if (!string.IsNullOrWhiteSpace(cityCode))
            {
                wheres.Add("CityCode =?CityCode");
                parameters.Add("?CityCode", cityCode.Trim(), DbType.AnsiString);
            }
            if (!string.IsNullOrWhiteSpace(countyCode))
            {
                wheres.Add("CountyCode =?CountyCode");
                parameters.Add("?CountyCode", countyCode.Trim(), DbType.AnsiString);
            }

            wheres.Add("AgentRecordId =?AgentRecordId");
            parameters.Add("?AgentRecordId", agentRecordId, DbType.Int64);

            wheres.Add("SpecificServiceTypeId =?SpecificServiceTypeId");
            parameters.Add("?SpecificServiceTypeId", specificServiceTypeId, DbType.Int64);

            string sql = string.Format(@" SELECT COUNT(1) FROM specificservicetypeoverwrite   {0} ",
                                    wheres.Count > 0 ? " WHERE " + string.Join(" AND ", wheres) : string.Empty);

            return await this.database.Slave.ExecuteScalarAsync<int>(sql, parameters);
        }
        #endregion
    }
}
//接口



